﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using System.Threading.Tasks;
using Group3.MediaTool.Interface;
using Group3.MediaTool.Core;
using System.Data;
using System.ComponentModel;
using Group3.MediaTool.Data.IO;

namespace Group3.MediaTool.Provider
{

    public class SQLDatabaseProvider : DataStoreProvider
    {
        #region override DataStoreProvider
        //Create global variables
        SqlConnection con = new SqlConnection();

        public SQLDatabaseProvider()
        {

        }

        /// <summary>
        /// creates the SQL-Database in case it does not exist. Database FIle will be named Group3_BookTool 
        /// </summary>
        public void createDatabase()
        {
            String str;
            //Create temporary database connection to Host DB Master 
            SqlConnection tempCon = new SqlConnection(StaticConfig.masterDatabaseConnection);

            //SQL Statement to create database
            string databaseName = (StaticConfig.databaseConnection.Split('=').Last());
            str = " IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = '"+databaseName+"') " +
                " CREATE DATABASE [" +databaseName+ "]";
            //Run Database creation SQLCommand 
            SqlCommand sqlCmd = new SqlCommand(str, tempCon);

            //Create DataDefinition from File
            DataDefinitionIO ddIO = new DataDefinitionIO();
            try
            {
                //Open the temporary connection to Host DB Master
                tempCon.Open();
                sqlCmd.ExecuteNonQuery();
                ddIO.writeLogFile("Database is Created Successfully", "Info");
                //Set connection string to created database
                base.Connect = StaticConfig.databaseConnection;
                con.ConnectionString = base.Connect;
            }
            catch (Exception e)
            {
                //On error write logfile and close temporary connection
                ddIO.writeLogFile("Database not Created:  " + e.ToString(), "Error");
                if (tempCon.State == ConnectionState.Open) { tempCon.Close(); }
            }
            finally
            {
                //if successfully created close HOST DB Master connection
                if (tempCon.State == ConnectionState.Open) { tempCon.Close(); }
            }

        }

        /// <summary>
        /// the connection to the database is created and database connection string set if not allready set
        /// </summary>
        private void initialiseConnection()
        {
            // if connection string is not set get connection from base
            if (String.IsNullOrEmpty(this.Connect))
            {
                this.Connect = base.Connect;
                con.ConnectionString = base.Connect;
                if (String.IsNullOrEmpty(base.Connect))
                {
                    base.Connect = StaticConfig.databaseConnection;
                    con.ConnectionString = base.Connect;
                }
            }
            else
            {
                con.ConnectionString = this.Connect;
            }

            try
            {
                //open database connection 
                con.Open();
            }
            catch (Exception e)
            {
                DataDefinitionIO ddIO = new DataDefinitionIO();
                ddIO.writeLogFile(e.ToString(), "Error");
                if (con.State == ConnectionState.Open) { con.Close(); }
                return;
            }
            return;
        }

        /// <summary>
        /// Drop all tables created in the SQL database
        /// </summary>
        public void dropAllTabels()
        {
            //Clear database
            String dropAllTables = "declare @command varchar(15)"
                        + "set @command = 'drop table ?'"
                        + "exec sp_msforeachtable @command";

            runCommand(dropAllTables);
        }



        public override bool ExecuteAction<T>(Action<T> action, T parameter)
        {
            return base.ExecuteAction<T>(action, parameter);
        }

        /// <summary>
        /// Runs a String as an SQL-Command against the database
        /// </summary>
        /// <param name="pSqlCommand">SQL-Command</param>
        /// <returns>True if successfull, false if unsuccessful</returns>
        public bool runCommand(String pSqlCommand)
        {
            int result = -1;
            initialiseConnection();
            //start transaction for Command
            SqlTransaction trans = con.BeginTransaction();
            try
            {
                // ANSI SQL way.  Works in PostgreSQL, MSSQL, MySQL. 
                //create the command object
                var cmd = con.CreateCommand();
                cmd.Transaction = trans;
                cmd.CommandText = pSqlCommand;
                //execute command and commit 
                result = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (Exception e)
            {
                //rollback on transaktion and writing logfile on error
                trans.Rollback();
                DataDefinitionIO ddIO = new DataDefinitionIO();

                ddIO.writeLogFile(e.ToString(), "Error");
                ddIO.writeLogFile("SQL-Command: " + pSqlCommand, "Error");
                if (con.State == ConnectionState.Open) { con.Close(); }
                return false;
            }
            //close connection to database
            if (con.State == ConnectionState.Open) { con.Close(); }

            if (result > 0)
            { return true; }
            else { return false; }
        }

        /// <summary>
        /// Runs a String as an SQL-Command against the database
        /// </summary>
        /// <param name="pSqlCommand">SQL-Command</param>
        /// <param name="paramName">Parameter in SQL-Command</param>
        /// <param name="result">Return value of parameter</param>
        /// <returns></returns>
        public bool runCommand(String pSqlCommand, String paramName, out int result)
        {
            result = -1;
            initialiseConnection();
            SqlTransaction trans = con.BeginTransaction();
            SqlParameter param = new SqlParameter("@" + paramName, SqlDbType.Int);
            param.Direction = ParameterDirection.Output;


            try
            {
                // ANSI SQL way.  Works in PostgreSQL, MSSQL, MySQL.  
                var cmd = con.CreateCommand();
                cmd.Transaction = trans;
                cmd.CommandText = pSqlCommand;
                cmd.Parameters.Add(param);

                cmd.CommandType = CommandType.Text;
                var test = cmd.ExecuteScalar();
                trans.Commit();
                if (paramName == "")
                {
                    if (test != null)
                    {
                        result = (int)test;
                    }
                }
                else
                {
                    if (paramName == "Id")
                    {
                        SqlDataReader tempRes = cmd.ExecuteReader();
                        if (tempRes.Read())
                        {
                            result = int.Parse(tempRes["ID"].ToString());
                        }
                    }
                    else
                    {
                        result = (int)cmd.Parameters["@" + paramName].Value;
                    }
                }
                cmd.Parameters.Clear();
            }
            catch (Exception e)
            {
                trans.Rollback();

                DataDefinitionIO ddIO = new DataDefinitionIO();
                ddIO.writeLogFile(e.ToString(), "Error");
                ddIO.writeLogFile("SQL-Command: " + pSqlCommand, "Error");
                if (con.State == ConnectionState.Open) { con.Close(); }
                return false;
            }
            if (con.State == ConnectionState.Open) { con.Close(); }

            return true;
        }

        /// <summary>
        /// Run SQL Command with up to on integer return value
        /// </summary>
        /// <param name="pSqlCommand"> Parameterized SQL Command </param>
        /// <param name="para">List of SQLParameter containing values for parameters</param>
        /// <param name="paramName">Empty: No return value, Otherwise: Name of the parameter specified in on of the SqlPArameters without @</param>
        /// <param name="result">integer return value</param>
        /// <returns></returns>
        public bool runCommand(String pSqlCommand, List<SqlParameter> para, String paramName, out int result)
        {
            result = -1;
            initialiseConnection();
            SqlTransaction trans = con.BeginTransaction();
            try
            {
                // ANSI SQL way.  Works in PostgreSQL, MSSQL, MySQL.  
                var cmd = con.CreateCommand();
                cmd.Transaction = trans;
                cmd.CommandText = pSqlCommand;

                foreach (SqlParameter parm in para)
                {
                    cmd.Parameters.Add(parm);
                }

                cmd.CommandType = CommandType.Text;
                var test = cmd.ExecuteScalar();
                trans.Commit();
                if (paramName == "")
                {
                    if (test != null)
                    {
                        result = (int)test;
                    }
                }
                else
                {
                    if (paramName == "Id")
                    {
                        SqlDataReader tempRes = cmd.ExecuteReader();
                        if (tempRes.Read())
                        {
                            result = int.Parse(tempRes["ID"].ToString());
                        }
                    }
                    else
                    {
                        result = (int)cmd.Parameters["@" + paramName].Value;
                    }
                }
                cmd.Parameters.Clear();
            }
            catch (Exception e)
            {
                trans.Rollback();

                DataDefinitionIO ddIO = new DataDefinitionIO();
                ddIO.writeLogFile(e.ToString(), "Error");
                ddIO.writeLogFile("SQL-Command: " + pSqlCommand, "Error");
                if (con.State == ConnectionState.Open) { con.Close(); }
                return false;
            }
            if (con.State == ConnectionState.Open) { con.Close(); }

            return true;

        }

        private List<string> getRefForProperty(String _className, String propertyName)
        {
            //get definition from definition file 
            XDocument doc = StaticConfig.classConfig;
            //get all classes from definition file
            IEnumerable<XElement> mainElements = from c in doc.Root.Elements("class")
                                                 select c;
            XElement mainElement = null;


            //loop through all and get specified class definition
            foreach (XElement ele in mainElements)
            {
                if (ele.Element("name").Value.ToUpper() == _className.ToUpper())
                {
                    mainElement = ele;
                    break;
                }
            }

            //select only refAttribute definition for propertyname for specified class 
            if (mainElements != null && mainElements.Count() > 0)
            {

                mainElement = (from e in mainElement.Elements("refAttribute")
                               where e.Element("name").Value.ToUpper() == propertyName.ToUpper()
                               select e).First();

            }

            if (mainElement != null)
            {
                List<String> sArr = new List<string>(); ;
                sArr.Add(mainElement.Element("refTable").Value);
                sArr.Add(mainElement.Element("refClass").Value);
                return sArr;
            }
            else
            {
                return new List<string>();
            }
        }

        public override IEnumerable<IObjectRelationModel> GetDataFromStore<T>()
        {
            //Open a Datastore
            //i.e.: d:\test.db

            List<IObjectRelationModel> result = new List<IObjectRelationModel>();
            initialiseConnection();

            //get database table name from classname
            String _className = ((typeof(T).ToString()).Split('_'))[1].ToUpper();
            try
            {
                //create select statement for all entries in table
                String sqlSelect = String.Concat("SELECT * FROM ", _className);

                //create SQL-Command object and execute
                SqlCommand cmd = new SqlCommand(sqlSelect, con);
                SqlDataReader reader = cmd.ExecuteReader();

                //read all obects from database
                while (reader.Read())
                {
                    //create an instance of the Object and loop through all properties
                    IObjectRelationModel on = (T)Activator.CreateInstance(typeof(T));
                    IEnumerable<String> properties = on.GetPropertyNames();

                    foreach (String s in properties)
                    {
                        String refTable = "";
                        String refClass = "";
                        var onValue = on.GetValue(s);
                        //If the property is generic it's a list and the relations have to be read as well
                        if (onValue != null && ((onValue).GetType()).IsGenericType)
                        {
                            //get reference table and reference classname from config file
                            List<String> tempArr = getRefForProperty(_className, s);
                            refTable = tempArr[0].ToUpper();	//Store tablename where references are stored
                            refClass = tempArr[1];				//get class which is referenced
                        }
                        if (refTable != "")
                        {
                            //create new Database connection and read ID of referenced classes 
                            SqlConnection refcon = new SqlConnection();
                            refcon.ConnectionString = base.Connect;
                            refcon.ConnectionString = con.ConnectionString;
                            SqlCommand refCmd = refcon.CreateCommand();
                            refCmd.CommandText = String.Concat("SELECT * FROM ", refClass,
                                " WHERE ID IN (SELECT ", refClass.ToUpper(), " FROM ", refTable, " WHERE ", _className + " = @classNameId )");

                            SqlParameter param = refCmd.CreateParameter();
                            //Set output parameter to retrieve ID
                            param.ParameterName = "@classNameId";
                            param.Value = on.GetValue("Id");

                            refCmd.Parameters.Add(param);
                            //open database connection
                            refcon.Open();
                            SqlDataReader refReader = refCmd.ExecuteReader();

                            List<IObjectRelationModel> refList = new List<IObjectRelationModel>();
                            //read all from reference table
                            while (refReader.Read())
                            {
                                IEnumerable<IObjectRelationModel> test = onValue as IEnumerable<IObjectRelationModel>;

                                ObjectTypeProvider op = new ObjectTypeProvider();
                                Type X = op.getClassTypeFromString(refClass);

                                //FOR UNITTESTS
                                if (X == null)
                                {
                                    //FOR TESTS//
                                    //get type of referenced class from assembly
                                    //String className = String.Concat("Group3.MediaTool.Core.E_", refClass, ", ", StaticConfig.classAssembly);
                                    String className = String.Concat("Group3.MediaTool.Core.E_", refClass, ", ", "Group3.MediaTool.TestClassenBibliothek, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null");
                                    X = Type.GetType(className, true, true);
                                    //FOR TESTS//
                                }


                                //create object of referenced Class and all properties
                                IObjectRelationModel refOn = (IObjectRelationModel)Activator.CreateInstance(X);
                                IEnumerable<String> refProperties = refOn.GetPropertyNames();

                                //Loop through propertied
                                foreach (String refS in refProperties)
                                {
                                    //if reference property --> ignore; No loops 
                                    if (refOn.GetValue(refS) != null && ((refOn.GetValue(refS)).GetType()).IsGenericType)
                                    { }
                                    else
                                    {	//if modified 
                                        if (refS.ToUpper() == "MODIFIED" || refS.ToUpper() == "CREATED")
                                        {
                                            //TODO reader OR refReader??
                                            refOn.SetValue(refS, reader[refS].ToString());
                                        }
                                        else
                                        {
                                            //get value from reader and set on object
                                            refOn.SetValue(refS, refReader[refS]);
                                        }
                                    }
                                }
                                // add object to List
                                refList.Add(refOn);
                            }
                            //set reference attribute
                            on.SetValue(s, refList);
                            //close database connection 
                            refcon.Close();

                        }
                        else
                        {
                            if (s.ToUpper() == "MODIFIED" || s.ToUpper() == "CREATED")
                            {
                                //set value as string 
                                on.SetValue(s, reader[s].ToString());
                            }
                            else
                            {
                                //set value
                                on.SetValue(s,reader[s] );
                            }
                        }
                    }
                    result.Add(on);
                }
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }

                return result;
            }
            catch (Exception e)
            {
                DataDefinitionIO ddIO = new DataDefinitionIO();
                ddIO.writeLogFile(e.ToString(), "Error");
                if (con.State == ConnectionState.Open) { con.Close(); }
                return null;
            }

        }

        public override IEnumerable<IObjectRelationModel> GetDataFromStore<T>(Predicate<T> filterstmt)
        {
            List<IObjectRelationModel> result = new List<IObjectRelationModel>();
            IEnumerable<IObjectRelationModel> fullResult = GetDataFromStore<T>();

            try
            {
                if (filterstmt == null)
                    return GetDataFromStore<IObjectRelationModel>();

                result = (from c in fullResult.AsQueryable<IObjectRelationModel>()
                          //where filterstmt((IObjectRelationModel)c)
                          select (IObjectRelationModel)c).ToList();
                return result;
            }
            catch (Exception e)
            {
                DataDefinitionIO ddIO = new DataDefinitionIO();
                ddIO.writeLogFile(e.ToString(), "Error");
                if (con.State == ConnectionState.Open) { con.Close(); }
                return null;
            }
        }


        //TODO Summary
        public override bool SaveChanges(IObjectRelationModel pOrm)
        {
            //get connection string 
            con.ConnectionString = base.Connect;
            IObjectRelationModel orm = pOrm;
            Type t = orm.GetType();
            bool returnVal = true;
            bool idnotSetRefLater = false;

            //get database table name from classname
            String _class = (((t.ToString()).Split('_'))[1] + " ").ToUpper();
            String _classID = "";
            //if ID is set store id in variable
            if (orm.GetValue("Id") != null)
            {
                _classID = orm.GetValue("Id").ToString().ToUpper();
            }
            //get all properties of class
            IEnumerable<String> properties = orm.GetPropertyNames();
            String _sqlUpdate = "";

            //IF id is not set, the object was not loaded from database. Database entry has to be created. 
            if (String.IsNullOrEmpty(_classID))
            {
                //Initialize SQL Parameter list
                List<SqlParameter> parIns = new List<SqlParameter>();
                //Create Inster stub
                _sqlUpdate = String.Concat("INSERT INTO ", _class);
                String _columns = "( ";
                String _values = "( ";

                foreach (String s in properties)
                {
                    //set special datetime values for creation of entry and modified entry
                    if (s.ToUpper() == "CREATED") { orm.SetValue(s, DateTime.Now.ToString()); }
                    if (s.ToUpper() == "MODIFIED") { orm.SetValue(s, DateTime.Now.ToString()); }

                    var ormVal = orm.GetValue(s);
                    if (ormVal != null)
                    {
                        if (((ormVal).GetType()).IsGenericType)
                        {
                            if (((IEnumerable<IObjectRelationModel>)ormVal).Count() > 0)
                            {
                                if (String.IsNullOrEmpty(_classID))
                                {
                                    // if entry is new set flag to true, that references will be created after the creation of the object
                                    idnotSetRefLater = true;
                                }
                                else
                                {
                                    //if object exists create entries in refTable
                                    makeEntriesInRefTable(pOrm, s);
                                }
                            }
                        }
                        else
                        {
                            _columns = String.Concat(_columns, s.ToUpper(), ", ");
                            _values = String.Concat(_values, "@", s.ToUpper(), ", ");

                            //get DB type of property
                            Tuple<SqlDbType, int> tup = getDBType(s);

                            if (tup.Item2 != -1)
                            {

                                SqlParameter sqlParameter = new SqlParameter("@" + s.ToUpper(), tup.Item1, tup.Item2);
                                sqlParameter.Value = orm.GetValue(s);
                                parIns.Add(sqlParameter);
                            }
                            else
                            {
                                SqlParameter sqlParameter = new SqlParameter("@" + s.ToUpper(), tup.Item1);
                                sqlParameter.Value = orm.GetValue(s);
                                parIns.Add(sqlParameter);
                            }
                        }
                    }
                }
                _columns = _columns.Remove(_columns.Length - 2, 2) + " )";
                _values = _values.Remove(_values.Length - 2, 2) + " )";
                int id = -1;
                if (!String.IsNullOrEmpty(_values))
                {
                    _sqlUpdate = String.Concat(_sqlUpdate, _columns, " VALUES ", _values);
                    _sqlUpdate = String.Concat(_sqlUpdate, " SET @newId = SCOPE_IDENTITY(); ");

                    SqlParameter paraReturn = new SqlParameter("@newId", SqlDbType.Int);
                    paraReturn.Direction = ParameterDirection.Output;
                    parIns.Add(paraReturn);
                    returnVal = runCommand(_sqlUpdate, parIns, "newId", out id);
                }
                orm.SetValue("Id", id);
                if (id > 0)
                {
                    if (idnotSetRefLater)
                    {
                        foreach (String s in properties)
                        {
                            var ormVal = orm.GetValue(s);
                            if (ormVal != null)
                            {
                                if (((ormVal).GetType()).IsGenericType)
                                {
                                    if (((IEnumerable<IObjectRelationModel>)ormVal).Count() > 0)
                                    {
                                        makeEntriesInRefTable(pOrm, s);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            else
            {
                List<SqlParameter> parUpdate = new List<SqlParameter>();
                _sqlUpdate = String.Concat("UPDATE ", _class, " SET ");
                String _setValues = "";
                foreach (String s in properties)
                {
                    if (s.ToUpper() != "ID" && orm.GetValue(s) != null)
                    {
                        if (((orm.GetValue(s)).GetType()).IsGenericType)
                        {
                            makeEntriesInRefTable(pOrm, s);
                        }
                        else
                        {
                            _setValues = String.Concat(_setValues, " ", s.ToUpper(), "= @", s.ToUpper(), ", ");
                            if (s.ToUpper() == "MODIFIED") { orm.SetValue(s, DateTime.Now.ToString()); }

                            Tuple<SqlDbType, int> tup = getDBType(s);

                            if (tup.Item2 != -1)
                            {

                                SqlParameter sqlParameter = new SqlParameter("@" + s.ToUpper(), tup.Item1, tup.Item2);
                                sqlParameter.Value = orm.GetValue(s);
                                parUpdate.Add(sqlParameter);
                            }
                            else
                            {
                                SqlParameter sqlParameter = new SqlParameter("@" + s.ToUpper(), tup.Item1);
                                sqlParameter.Value = orm.GetValue(s);
                                parUpdate.Add(sqlParameter);
                            }
                        }
                    }
                }
                _setValues = _setValues.Remove(_setValues.Length - 2, 2) + "";
                _setValues = String.Concat(_setValues, " WHERE ID = '", _classID + "'");
                _sqlUpdate = String.Concat(_sqlUpdate, _setValues);
                int i;
                returnVal = runCommand(_sqlUpdate, parUpdate, "", out i);
                pOrm.SetValue("Id", i);

            }
            return returnVal;
        }

        private String makeEntriesInRefTable(Interface.IObjectRelationModel pOrm, String property)
        {
            String _sqlMultiUpdate = "";
            List<KeyValuePair<SqlParameter, SqlParameter>> _multiVal = new List<KeyValuePair<SqlParameter, SqlParameter>>();
            Type t = pOrm.GetType();
            String _class = (((t.ToString()).Split('_'))[1]).ToUpper();
            List<string> refArr = getRefForProperty(_class, property);
            String refTable = refArr[0].ToUpper();
            String refClass = refArr[1].ToUpper();
            String checkSQL;
            Boolean completed = false;

            _sqlMultiUpdate = String.Concat(_sqlMultiUpdate, "INSERT INTO ", refTable, " ( ", _class, ", ", refClass, " ) VALUES (@", _class, ", @", refClass, ") SET @newId = SCOPE_IDENTITY();");

            List<int> refIds = new List<int>();
            foreach (IObjectRelationModel itm in (pOrm.GetValue(property) as IEnumerable<IObjectRelationModel>))
            {
                List<SqlParameter> references = new List<SqlParameter>();
                checkSQL = String.Concat("SELECT ID FROM ", refTable, " WHERE ", _class, "= @", _class, " AND ", refClass, "= @", refClass);

                SqlParameter sqlParam1 = new SqlParameter("@" + _class, SqlDbType.BigInt);
                sqlParam1.Value = pOrm.GetValue("Id");
                references.Add(sqlParam1);

                SqlParameter sqlParam2 = new SqlParameter("@" + refClass, SqlDbType.BigInt);
                sqlParam2.Value = itm.GetValue("Id");
                references.Add(sqlParam2);

                int i;
                runCommand(checkSQL, references, "Id", out i);
                refIds.Add(i);


                if (i < 0)
                {
                    KeyValuePair<SqlParameter, SqlParameter> kvp = new KeyValuePair<SqlParameter, SqlParameter>(sqlParam1, sqlParam2);
                    _multiVal.Add(kvp);
                }
                references = null;
            }

            foreach (KeyValuePair<SqlParameter, SqlParameter> kv in _multiVal)
            {
                int i;
                List<SqlParameter> para = new List<SqlParameter>();
                para.Add(kv.Key);
                para.Add(kv.Value);

                SqlParameter paraReturn = new SqlParameter("@newId", SqlDbType.Int);
                paraReturn.Direction = ParameterDirection.Output;
                para.Add(paraReturn);

                completed = runCommand(_sqlMultiUpdate, para, "newId", out i);
                refIds.Add(i);
            }

            String sqlCmd = String.Concat("DELETE FROM ", refTable, " WHERE ID NOT IN (");
            foreach (int s in refIds)
            {
                sqlCmd = String.Concat(sqlCmd, "'", s, "', ");
            }
            sqlCmd = sqlCmd.Remove(sqlCmd.Length - 2, 2) + ");";
            completed = completed && runCommand(sqlCmd);

            return _sqlMultiUpdate;
        }

        private Tuple<SqlDbType, int> getDBType(string propert)
        {

            XDocument doc = StaticConfig.classConfig;
            //XElement mainElement = sele(e => e.Element("name").Value == );

            IEnumerable<String> dbType = from c in doc.Descendants("class").Descendants("attribute")
                                         where c.Element("name").Value.ToUpper() == propert.ToUpper()
                                         select c.Element("type").Value;
            SqlDbType sdbTyp = SqlDbType.NVarChar;
            int size = -1;

            if (propert == "Id")
            {
                sdbTyp = SqlDbType.BigInt;
            }
            else
            {
                if (propert == "Created" || propert == "Modified") { sdbTyp = SqlDbType.DateTime; }
                //else { sdbTyp = SqlDbType.Variant; }
            }

            if (dbType.Count() > 0)
            {
                String[] getInfo = (dbType.First()).Split('(');
                if (getInfo.Count() > 1 && !String.IsNullOrEmpty(getInfo[1]))
                {
                    try
                    {
                        String s = getInfo[1].Substring(0, getInfo[1].Length - 1);
                        if (s != "MAX")
                        {
                            size = (int)Convert.ToInt64(getInfo[1].Split(')')[0]);
                        }
                        else
                        {
                            return new Tuple<SqlDbType, int>(SqlDbType.NVarChar, -1);
                        }
                    }
                    catch (Exception e)
                    {
                        DataDefinitionIO ddio = new DataDefinitionIO();
                        ddio.writeLogFile(e.ToString(), "Error");
                        //do nothing
                    }
                }
                #region Switch
                switch (getInfo[0].ToLower())
                {
                    case "bit":
                        sdbTyp = SqlDbType.Bit;
                        break;
                    case "real":
                        sdbTyp = SqlDbType.Real;
                        break;
                    case "float":
                        sdbTyp = SqlDbType.Float;
                        break;
                    case "tinyint":
                        sdbTyp = SqlDbType.TinyInt;
                        break;
                    case "smallint":
                        sdbTyp = SqlDbType.SmallInt;
                        break;
                    case "int":
                        sdbTyp = SqlDbType.Int;
                        break;
                    case "bigint":
                        sdbTyp = SqlDbType.BigInt;
                        break;
                    //case "numeric":
                    //   sdbTyp =  SqlDbType.;
                    //    break;
                    case "binary":
                        sdbTyp = SqlDbType.Binary;
                        break;
                    case "varbinary":
                        sdbTyp = SqlDbType.VarBinary;
                        break;
                    case "image":
                        sdbTyp = SqlDbType.Image;
                        break;
                    case "char":
                        sdbTyp = SqlDbType.Char;
                        break;
                    case "varchar":
                        sdbTyp = SqlDbType.VarChar;
                        break;
                    case "text":
                        sdbTyp = SqlDbType.Text;
                        break;
                    case "nchar":
                        sdbTyp = SqlDbType.NChar;
                        break;
                    case "nvarchar":
                        sdbTyp = SqlDbType.NVarChar;
                        break;
                    case "ntext":
                        sdbTyp = SqlDbType.NText;
                        break;
                    case "datetime":
                        sdbTyp = SqlDbType.DateTime;
                        break;
                    case "smalldatetime":
                        sdbTyp = SqlDbType.SmallDateTime;
                        break;
                    case "date":
                        sdbTyp = SqlDbType.Date;
                        break;
                    case "time":
                        sdbTyp = SqlDbType.Time;
                        break;
                    case "datetime2":
                        sdbTyp = SqlDbType.DateTime2;
                        break;
                    case "datetimeoffset":
                        sdbTyp = SqlDbType.DateTimeOffset;
                        break;
                    case "smallmoney":
                        sdbTyp = SqlDbType.SmallMoney;
                        break;
                    case "money":
                        sdbTyp = SqlDbType.Money;
                        break;
                    default:
                        sdbTyp = SqlDbType.Variant;
                        break;
                }
                #endregion

            }
            return new Tuple<SqlDbType, int>(sdbTyp, size);
        }


    }

        #endregion
}